﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using DBA.Data;
using DBA.Data.ADO;
using DBA.Bll;
using DBA.Common;

namespace DBA.Web.ProjectManage
{
    public partial class PorjectList : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack) return;
            ddlBind();
            Bind(1, null);
            sumMoney(null);
        }

        protected void ddlBind()
        {
            BaseCodeDDL.DdlInit(ddlXi, "Xi");//绑定系      

        }

        protected void linkBtnSearch_Click(object sender, EventArgs e)
        {
            IDictionary<String, String> dict = new Dictionary<String, String>();
            dict.Add("sp_Name", txtsp_Name.Text.Trim());
            dict.Add("sp_Person", txtsp_Person.Text.Trim());
            dict.Add("sp_Money", txtsp_Money.Text.Trim());
            dict.Add("sp_Time", txtsp_Time.Value.Trim());
            dict.Add("sp_EndTime", txtsp_EndTime.Value.Trim());
            dict.Add("sp_Class", ddl_sp_Class.SelectedValue);
            dict.Add("sp_Year", txt_Year.Text.Trim());
            dict.Add("Xi_ID", ddlXi.SelectedValue);
            ViewState["conditions"] = dict;
            Bind(1, dict);
            sumMoney(dict);
        }

        protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
        {
            IDictionary<String, String> dict = null;
            if (ViewState["conditions"] != null)
            {
                dict = (IDictionary<String, String>)ViewState["conditions"];
            }
            Bind(e.NewPageIndex, dict);


        }

        private void Bind(int pageIdx, IDictionary<String, String> dict)
        {
            DataAccessLibrary dal = CommonQuery.DataAccessLibrary;
            dal.ManagerParameter.ClearParameter();

            string sql = "select * from (select a.*,b.name,sumYearMoney=(select isnull(SUM(ym_Money),0) from YearMoney b where a.sp_ID=b.sp_ID ";
            if (dict != null)
            {
                if (!dict["sp_Year"].ToString().Equals(string.Empty))
                {
                    sql += " and SUBSTRING(b.ym_year,1,4) = @sp_Year";
                    dal.ManagerParameter.AddNewInParameter("sp_Year", dict["sp_Year"].ToString());
                }
            }
            sql += "),  ";
            sql += "attCount=(select count(1) from PorjectAttachment b where a.sp_ID=b.sp_ID) from SinProject a  ";
            sql += " left join EmployeeInfo b on a.sp_person=b.EmployeeID where 1=1 ";
          
            if (dict != null)
            {
                if (!dict["sp_Name"].ToString().Equals(string.Empty))
                {
                    sql += " and a.sp_Name like '%'+@sp_Name+'%'";
                    dal.ManagerParameter.AddNewInParameter("sp_Name", dict["sp_Name"].ToString());
                }
                if (!dict["sp_Person"].ToString().Equals(string.Empty))
                {
                    sql += " and b.Name like '%'+@sp_Person+'%'";
                    dal.ManagerParameter.AddNewInParameter("sp_Person", dict["sp_Person"].ToString());
                }

                if (!dict["sp_Money"].ToString().Equals(string.Empty))
                {
                    sql += " and a.sp_Money like '%'+@sp_Money+'%'";
                    dal.ManagerParameter.AddNewInParameter("sp_Money", dict["sp_Money"].ToString());
                }

                if (!dict["sp_Class"].ToString().Equals("-1"))
                {
                    sql += " and a.sp_Class =@sp_Class";
                    dal.ManagerParameter.AddNewInParameter("sp_Class", dict["sp_Class"].ToString());
                }

                if (!dict["sp_Time"].ToString().Equals(string.Empty))
                {
                    sql += " and a.sp_Time >= @sp_Time";
                    dal.ManagerParameter.AddNewInParameter("sp_Time", dict["sp_Time"].ToString());
                }

                if (!dict["sp_EndTime"].ToString().Equals(string.Empty))
                {
                    sql += " and a.sp_EndTime >= @sp_EndTime";
                    dal.ManagerParameter.AddNewInParameter("sp_EndTime", dict["sp_EndTime"].ToString());
                }

                if (!dict["Xi_ID"].ToString().Equals("-1"))
                {
                    sql += " and b.xi_id = @xi_id";
                    dal.ManagerParameter.AddNewInParameter("xi_id", dict["Xi_ID"].ToString());
                }
              
            }

            sql += ") aa";
            if (dict != null)
            {
                  if (!dict["sp_Year"].ToString().Equals(string.Empty))
                {
                    sql += " where sumYearMoney>0 ";
                }
            }


            int iCount = 0;

            replData.DataSource = dal.ModifyData.GetPageDataTable("(" + sql + ")", "*", "", "sp_Class,sp_Time DESC,sp_ID DESC", pageIdx, 10, ManagerSQL.PageViewTableType.MixtureTable, ref iCount);
            replData.DataBind();
            AspNetPager1.RecordCount = iCount;

        }

        private void sumMoney(IDictionary<String, String> dict) 
        {
            DataAccessLibrary dal = CommonQuery.DataAccessLibrary;
            dal.ManagerParameter.ClearParameter();

            string sql = "select isnull(sum(c.ym_Money),0) as sumMoney";
            sql += " from SinProject a inner join YearMoney c on a.sp_ID=c.sp_ID  ";
            sql += " left join EmployeeInfo b on a.sp_person=b.EmployeeID where 1=1 ";

            if (dict != null)
            {
                if (!dict["sp_Year"].ToString().Equals(string.Empty))
                {
                    sql += " and SUBSTRING(c.ym_year,1,4) = @sp_Year";
                    dal.ManagerParameter.AddNewInParameter("sp_Year", dict["sp_Year"].ToString());
                }

                if (!dict["sp_Name"].ToString().Equals(string.Empty))
                {
                    sql += " and a.sp_Name like '%'+@sp_Name+'%'";
                    dal.ManagerParameter.AddNewInParameter("sp_Name", dict["sp_Name"].ToString());
                }
                if (!dict["sp_Person"].ToString().Equals(string.Empty))
                {
                    sql += " and b.Name like '%'+@sp_Person+'%'";
                    dal.ManagerParameter.AddNewInParameter("sp_Person", dict["sp_Person"].ToString());
                }

                if (!dict["sp_Money"].ToString().Equals(string.Empty))
                {
                    sql += " and a.sp_Money like '%'+@sp_Money+'%'";
                    dal.ManagerParameter.AddNewInParameter("sp_Money", dict["sp_Money"].ToString());
                }

                if (!dict["sp_Class"].ToString().Equals("-1"))
                {
                    sql += " and a.sp_Class =@sp_Class";
                    dal.ManagerParameter.AddNewInParameter("sp_Class", dict["sp_Class"].ToString());
                }

                if (!dict["sp_Time"].ToString().Equals(string.Empty))
                {
                    sql += " and a.sp_Time >= @sp_Time";
                    dal.ManagerParameter.AddNewInParameter("sp_Time", dict["sp_Time"].ToString());
                }

                if (!dict["sp_EndTime"].ToString().Equals(string.Empty))
                {
                    sql += " and a.sp_EndTime >= @sp_EndTime";
                    dal.ManagerParameter.AddNewInParameter("sp_EndTime", dict["sp_EndTime"].ToString());
                }

                if (!dict["Xi_ID"].ToString().Equals("-1"))
                {
                    sql += " and b.xi_id = @xi_id";
                    dal.ManagerParameter.AddNewInParameter("xi_id", dict["Xi_ID"].ToString());
                }

               
            }
            hdTotle.Value = dal.ExecuteString(sql);
        }

        protected void linkBtnMaskSearch_Click(object sender, EventArgs e)
        {
            //IDictionary<String, String> dict = new Dictionary<String, String>();
            //dict.Add("UserName", txtMaskMemberId.Text.Trim());
            //ViewState["conditions"] = dict;
            //Bind(1, dict);
        }

        protected void linkBtnDeleSelect_Click(object sender, EventArgs e)
        {
            DataAccessLibrary dal = CommonQuery.DataAccessLibrary;
            if (!String.IsNullOrEmpty(hidDeleString.Value))
            {
                string value = hidDeleString.Value;
                string sql = "delete from [SinProject] where sp_id in (" + value + ");";
                dal.ExecuteNonQuery(sql);
                if (String.IsNullOrEmpty(dal.ErrorMessage))
                {
                    Bind(1, null);
                    ScriptManager.RegisterStartupScript(this, base.GetType(), "deleOk" + System.Guid.NewGuid().ToString(), "parent.alertMsg.correct('数据删除成功');", true);
                }
                else
                {
                    ScriptManager.RegisterStartupScript(this, base.GetType(), "deleOk" + System.Guid.NewGuid().ToString(), "parent.alertMsg.error('数据删除失败');", true);
                }
            }
        }

        protected void lnkDownload_Click(object sender, EventArgs e)
        {
            IDictionary<String, String> dict = new Dictionary<String, String>();
            dict.Add("sp_Name", txtsp_Name.Text.Trim());
            dict.Add("sp_Person", txtsp_Person.Text.Trim());
            dict.Add("sp_Money", txtsp_Money.Text.Trim());
            dict.Add("sp_Time", txtsp_Time.Value.Trim());
            dict.Add("sp_EndTime", txtsp_EndTime.Value.Trim());
            dict.Add("sp_Class", ddl_sp_Class.SelectedValue);
            dict.Add("sp_Year", txt_Year.Text.Trim());
            dict.Add("Xi_ID", ddlXi.SelectedValue);
            ViewState["conditions"] = dict;

            DataAccessLibrary dal = CommonQuery.DataAccessLibrary;
            dal.ManagerParameter.ClearParameter();

            string sql = "select * from (select a.sp_Name as 项目名称,sp_Number as 项目编号,b.name as 项目负责人,sp_Money as 项目来源,Convert(char(10),sp_Time,120) as 开始时间,Convert(char(10),sp_EndTime,120) as 结束时间,sp_ContractFunds as 合同经费,case sp_Class when '1' then '国家级' when '2' then '省部级' when '3' then '地方政府' when '4' then '横向' end  as 级别,到款经费=(select isnull(SUM(ym_Money),0) from YearMoney b where a.sp_ID=b.sp_ID ";
            if (!dict["sp_Year"].ToString().Equals(string.Empty))
            {
                sql += " and SUBSTRING(b.ym_year,1,4) = @sp_Year";
                dal.ManagerParameter.AddNewInParameter("sp_Year", dict["sp_Year"].ToString());
            }
            sql +=")  ";
            sql += " from SinProject a  ";
            sql += " left join EmployeeInfo b on a.sp_person=b.EmployeeID where 1=1 ";
          
            if (dict != null)
            {
                if (!dict["sp_Name"].ToString().Equals(string.Empty))
                {
                    sql += " and a.sp_Name like '%'+@sp_Name+'%'";
                    dal.ManagerParameter.AddNewInParameter("sp_Name", dict["sp_Name"].ToString());
                }
                if (!dict["sp_Person"].ToString().Equals(string.Empty))
                {
                    sql += " and b.Name like '%'+@sp_Person+'%'";
                    dal.ManagerParameter.AddNewInParameter("sp_Person", dict["sp_Person"].ToString());
                }

                if (!dict["sp_Money"].ToString().Equals(string.Empty))
                {
                    sql += " and a.sp_Money like '%'+@sp_Money+'%'";
                    dal.ManagerParameter.AddNewInParameter("sp_Money", dict["sp_Money"].ToString());
                }

                if (!dict["sp_Class"].ToString().Equals("-1"))
                {
                    sql += " and a.sp_Class =@sp_Class";
                    dal.ManagerParameter.AddNewInParameter("sp_Class", dict["sp_Class"].ToString());
                }

                if (!dict["sp_Time"].ToString().Equals(string.Empty))
                {
                    sql += " and a.sp_Time >= @sp_Time";
                    dal.ManagerParameter.AddNewInParameter("sp_Time", dict["sp_Time"].ToString());
                }

                if (!dict["sp_EndTime"].ToString().Equals(string.Empty))
                {
                    sql += " and a.sp_EndTime >= @sp_EndTime";
                    dal.ManagerParameter.AddNewInParameter("sp_EndTime", dict["sp_EndTime"].ToString());
                }
                if (!dict["Xi_ID"].ToString().Equals("-1"))
                {
                    sql += " and b.xi_id = @xi_id";
                    dal.ManagerParameter.AddNewInParameter("xi_id", dict["Xi_ID"].ToString());
                }

                
                sql += " ) aa ";

                if (dict != null)
                {
                    if (!dict["sp_Year"].ToString().Equals(string.Empty))
                    {
                        sql += " where 到款经费>0 order by 级别 ";
                    }
                }

            }
            DataTable dt = dal.ExecuteFillDataTable(sql);
            NPOIHelper.ExportByWeb(dt, string.Empty, "科研信息" + new Random().Next().ToString() + ".xls");
        }

        public string  getClass (string type)
        {
            string rtn = string.Empty;
            switch (type) 
            {
                case "1":
                    rtn = "国家级";
                    break;
                case "2":
                    rtn = "省部级";
                    break;
                case "3":
                    rtn = "地方政府";
                    break;
                case "4":
                    rtn = "横向";
                    break;

            }
            return rtn;
        }
    }
}